# Create "ClaimAmount" & "WAvgPaidWeek" variable

# Setup -----
library(tidyverse) 




########################################################################################
# Load Hurricane Michael NFIP flood insurance claims data from FEMA
# SOURCE: obtained via an Freedom of Information Act (FOIA) request
# The data has 3 data fields: 1) the census tract of the flooded property; 2) the date of the claim payment; 3) the dollar amount of the claim payment
# Load data as "NFIP_Date_Michael"




NFIP_Date_Michael = NFIP_Date_Michael %>%
  mutate(
    Payment.Date           = as.Date(Payment.Date),
    Payment.Month          = format(as.Date(Payment.Date), "%y-%m")
  )  %>%
  filter(!is.na(CensusTract) & 
         !is.na(Payment.Date) & 
         !is.na(Total.Net.Payment))




list_NFIP_Date_Michael = unique(NFIP_Date_Michael$CensusTract)            # list of census tracts
date.end.month       <- seq(as.Date("2018-11-01"),length=47,by="months")  # list of months





# Loop: for each tract, keep all claims paid by the end of each month, then calculate cumulative $ amount paid and average # of weeks that these claims had been paid 
NFIP_Michael_Claim = c()
for (i in 1:length(list_NFIP_Date_Michael)) {
  NFIP_Date_Michael_new_i = NFIP_Date_Michael %>% filter(CensusTract==list_NFIP_Date_Michael[i])  # select all claims paid to a tract i
  
  NFIP_Date_Michael_new_ij = c()
  for (j in 1:length(date.end.month)) {
    test_ij = NFIP_Date_Michael_new_i %>% 
      filter(Payment.Date < date.end.month[j]) %>%                                                # keep all claims paid before the end of a month j
      mutate(
        Payment.Time.MonthEnd = as.numeric(date.end.month[j] - 1 - Payment.Date)/7                # calculate # of weeks that a claim had been paid by the end of month j
        ) %>%
      summarise(
        ClaimAmount                        = sum(Total.Net.Payment),                              # calculate cumulative $ amount paid by the end of month j
        Cumsum.PaymentAmount.Time.MonthEnd = sum(Payment.Time.MonthEnd*Total.Net.Payment)         
      ) %>%
      mutate(
        CensusTract = list_NFIP_Date_Michael[i], 
        month       = format(date.end.month[j]-1, "%y-%m")
      )
    
    NFIP_Date_Michael_new_ij = bind_rows(NFIP_Date_Michael_new_ij, test_ij)
  }
  NFIP_Michael_Claim = bind_rows(NFIP_Michael_Claim, NFIP_Date_Michael_new_ij)
}


NFIP_Michael_Claim = NFIP_Michael_Claim %>% 
  mutate(
    WAvgPaidWeek = ifelse(ClaimAmount==0, 0, Cumsum.PaymentAmount.Time.MonthEnd/ClaimAmount)      # calculate dollar-value-weighted average # of weeks that these claims had been paid by the end of month j
  ) %>%
  select(CensusTract, month, ClaimAmount, WAvgPaidWeek)




save(NFIP_Michael_Claim, file="/02_Data/NFIP_Michael_Claim.Rdata")